﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Assessment.Management.SharedComponent.Params.ManagerParam;
using Assessment.Management.SharedComponent.Constants;
using Assessment.Management.SharedComponent.Entities;
using System.Data.SqlClient;

namespace Assessment.Management.DAO.CommonManagers
{
    public class PollDAO : BaseDaos
    {
        public PollDAO()
            : base()
        {
        }

        public void CheckConfig(PollParam param)
        {

            string query = "Select * from Config where Password =@Password and Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Password", param.Poll.Password);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Poll = base.ExecuteQueryGetObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
        public void CheckExitsDataConfig(PollParam param)
        {
            string query = @"select count(*) from Config 
                                             where Deleted   = @Deleted and 
                                                   TeacherID = @TeacherID and 
                                                   ClassID   = @ClassID and 
                                                   SubjectID = @SubjectID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@TeacherID", param.Poll.TeacherID);
            cmd.Parameters.AddWithValue("@ClassID", param.Poll.ClassID);
            cmd.Parameters.AddWithValue("@SubjectID", param.Poll.SubjectID);
            try
            {
                param.Poll.Count = base.ExecuteScalar(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetListPoll(PollParam param)
        {

            string query = @"Select t.TeacherName, cl.ClassName, s.SubjectName, c.Password, c.Deleted as State, c.ID, c.Version 
                                      from Config as c INNER JOIN Teachers as t ON  c.TeacherID = t.ID
                                                       INNER JOIN Classes as cl ON  c.ClassID   = cl.ID
                                                       INNER JOIN Subjects as s ON  c.SubjectID = s.ID
                                                       where c.Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listPoll = base.ExecuteQueryGetListObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetPollInput(PollParam param)
        {

            string query = @"Select t.TeacherName, cl.ClassName, s.SubjectName, c.TeacherID, C.SubjectID, c.ClassID
                                      from Config as c INNER JOIN Teachers as t ON  c.TeacherID = t.ID
                                                       INNER JOIN Classes as cl ON  c.ClassID   = cl.ID
                                                       INNER JOIN Subjects as s ON  c.SubjectID = s.ID
                                                       where c.Deleted=@Deleted and c.Password = @Password and
                                                             c.TeacherID = @TeacherID and c.ClassID = @ClassID and
                                                             c.SubjectID = @SubjectID";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@Password", param.Poll.Password);
            cmd.Parameters.AddWithValue("@TeacherID", param.Poll.TeacherID);
            cmd.Parameters.AddWithValue("@ClassID", param.Poll.ClassID);
            cmd.Parameters.AddWithValue("@SubjectID", param.Poll.SubjectID);
            try
            {
                param.Poll = base.ExecuteQueryGetObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetListPollByDepartment(PollParam param)
        {
            string query = @"Select t.TeacherName, cl.ClassName, s.SubjectName, c.Password, c.Deleted as State, c.ID, c.Version 
                                      from Config as c INNER JOIN Teachers as t ON  c.TeacherID = t.ID
                                                       INNER JOIN Classes as cl ON  c.ClassID   = cl.ID
                                                       INNER JOIN Subjects as s ON  c.SubjectID = s.ID
                                                       where c.Deleted=@Deleted and t.DepartmentID=@DepartmentID";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@DepartmentID", param.Poll.DepartmentID);
            try
            {
                param.listPoll = base.ExecuteQueryGetListObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CreatePoll(PollParam param)
        {
            string query = @"Insert into Config (ID, Password, TeacherID, ClassID, SubjectID, CreatedBy, CreatedDTG, Deleted, Version) 
                                        values(@ID, @Password, @TeacherID, @ClassID, @SubjectID, @CreatedBy, @CreatedDTG, @Deleted, @Version)";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@ID", param.Poll.ID);
            cmd.Parameters.AddWithValue("@Password", param.Poll.Password);
            cmd.Parameters.AddWithValue("@TeacherID", param.Poll.TeacherID);
            cmd.Parameters.AddWithValue("@ClassID", param.Poll.ClassID);
            cmd.Parameters.AddWithValue("@SubjectID", param.Poll.SubjectID);
            cmd.Parameters.AddWithValue("@CreatedBy", param.Poll.CreatedBy);
            cmd.Parameters.AddWithValue("@CreatedDTG", param.Poll.CreatedDTG);
            cmd.Parameters.AddWithValue("@Deleted", param.Poll.Deleted);
            cmd.Parameters.AddWithValue("@Version", param.Poll.Version);

            try
            {
                int count = base.ExecuteNonQuery(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void UpdatePoll(PollParam param, SqlCommand sqlCmd = null)
        {
            string query = @"Update Config 
                              Set Password       = @Password, 
                                  Version        = Version+1,
                                  UpdatedBy      = @UpdatedBy,
                                  UpdatedDTG     = @UpdatedDTG
                              Where ID           = @ID and Version = @Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }

            sqlCmd.CommandText = query;
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Password", param.Poll.Password);
            sqlCmd.Parameters.AddWithValue("@UpdatedBy", param.Poll.UpdatedBy);
            sqlCmd.Parameters.AddWithValue("@UpdatedDTG", param.Poll.UpdatedDTG);
            sqlCmd.Parameters.AddWithValue("@ID", param.Poll.ID);
            sqlCmd.Parameters.AddWithValue("@Version", param.Poll.Version);

            try
            {
                int count = base.ExecuteNonQuery(sqlCmd);
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void DeletePoll(PollParam param, SqlCommand sqlCmd = null)
        {
            var query = @"Update Config
                          Set Deleted        = @Deleted,
                              Version        = Version+1,
                              UpdatedBy      = @UpdatedBy,
                              UpdatedDTG     = @UpdatedDTG
                          Where ID = @ID and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }
            List<Poll> lstDeletePoll = param.listPoll;
            sqlCmd.CommandText = query;

            try
            {
                foreach (Poll item in lstDeletePoll)
                {
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@Deleted", item.Deleted);
                    sqlCmd.Parameters.AddWithValue("@Version", item.Version);
                    sqlCmd.Parameters.AddWithValue("@UpdatedBy", item.UpdatedBy);
                    sqlCmd.Parameters.AddWithValue("@UpdatedDTG", item.UpdatedDTG);
                    sqlCmd.Parameters.AddWithValue("@ID", item.ID);
                    int count = base.ExecuteNonQuery(sqlCmd);
                }
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void SearchPollSetting(PollParam param)
        {
            string query = @"Select t.TeacherName, cl.ClassName, s.SubjectName, c.Password, c.Deleted as State, c.ID, c.Version 
                                      from Config as c INNER JOIN Teachers as t ON  c.TeacherID = t.ID
                                                       INNER JOIN Classes as cl ON  c.ClassID   = cl.ID
                                                       INNER JOIN Subjects as s ON  c.SubjectID = s.ID
                                                       where (@TeacherName ='' or t.TeacherName like N'%'+@TeacherName+N'%') and
                                                             (@ClassName ='' or cl.ClassName like N'%'+@ClassName+N'%') and
                                                             (@SubjectName ='' or s.SubjectName like N'%'+@SubjectName+N'%') and 
                                                              c.Deleted=@Deleted";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@TeacherName", param.Poll.TeacherName);
            cmd.Parameters.AddWithValue("@ClassName", param.Poll.ClassName);
            cmd.Parameters.AddWithValue("@SubjectName", param.Poll.SubjectName);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listPoll = base.ExecuteQueryGetListObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetLastPollCode(PollParam param)
        {
            string query = "select top 1 ID from Config where Deleted=@Deleted order by CreatedDTG desc";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Poll = base.ExecuteQueryGetObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetPollByID(PollParam param)
        {
            string query = @"Select t.TeacherName, cl.ClassName, s.SubjectName, c.Password, c.Version 
                                      from Config as c INNER JOIN Teachers as t ON  c.TeacherID = t.ID
                                                       INNER JOIN Classes as cl ON  c.ClassID   = cl.ID
                                                       INNER JOIN Subjects as s ON  c.SubjectID = s.ID
                                                       where c.Deleted=@Deleted and c.ID=@ID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@ID", param.Poll.ID);

            try
            {
                param.Poll = base.ExecuteQueryGetObject<Poll>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
    }
}
